﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using Model.ModiFication;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL.ModiFDAL
{
   public class dal
    {
       private static string strCon = ConfigurationManager.ConnectionStrings["Constr"].ToString();
       SqlConnection op = new SqlConnection(strCon);
       /// <summary>
       /// 部门List
       /// </summary>
       /// <returns></returns>
       public List<Dept_Tab> Delist()
       {
           op.Open();
           string sql = "select * from Dept_Tab";
           SqlDataAdapter a = new SqlDataAdapter(sql, op);
           DataTable dt = new DataTable();
           a.Fill(dt);
           List<Dept_Tab> lst = new List<Dept_Tab>();
           for (int i = 0; i < dt.Rows.Count; i++)
           {
               Dept_Tab s = new Dept_Tab();
               s.DeptID = Convert.ToInt32(dt.Rows[i]["DeptID"]);
               s.DeptName = dt.Rows[i]["DeptName"].ToString();
               lst.Add(s);
           }
           return lst;
       }
       public Dept_Tab dept(int id)
       {
           op.Open();
           string sql = string.Format("select * from Dept_Tab where DeptID='{0}'",id);
           SqlCommand can = new SqlCommand(sql,op);
           SqlDataReader re = can.ExecuteReader();
           Dept_Tab s = new Dept_Tab();
           while(re.Read())  
           {
               s.DeptID = Convert.ToInt32(re["DeptID"]);
               s.DeptName = re["DeptName"].ToString();
           }
           re.Close();
           return s;
       }
       /// <summary>
       /// 职位List
       /// </summary>
       /// <returns></returns>
       public List<Position_Tab>Polist()
       {
           op.Open();
           string sql = "select * from Position_Tab";
           SqlDataAdapter a = new SqlDataAdapter(sql, op);
           DataTable dt = new DataTable();
           a.Fill(dt);
           List<Position_Tab> lst = new List<Position_Tab>();
           for (int i = 0; i < dt.Rows.Count; i++)
           {
               Position_Tab s = new Position_Tab();
               s.PositionName = dt.Rows[i]["PositionName"].ToString();
               s.PositionID = Convert.ToInt32(dt.Rows[i]["PositionID"]);
               lst.Add(s);
           }
           return lst;

       }
       public EmployeeLoginInfo_Tab Empl(int EmployID)
       {
           op.Open();
           string sql = string.Format("select a.*,b.DeptName,c.PositionName from EmployeeLoginInfo_Tab a,Dept_Tab b,Position_Tab c where a.DeptID=b.DeptID and a.PositionID=c.PositionID and a.EmployID='{0}'", EmployID);
           SqlCommand can = new SqlCommand(sql, op);
           SqlDataReader re = can.ExecuteReader();
           EmployeeLoginInfo_Tab s = new EmployeeLoginInfo_Tab();
           while (re.Read())
           {
               s.EmployID = Convert.ToInt32(re["EmployID"]);
               s.EmployName = re["EmployName"].ToString();
               s.DeptID = Convert.ToInt32(re["DeptID"]);
               s.DeptName = re["DeptName"].ToString();
               s.PositionID = Convert.ToInt32(re["PositionID"]);
               s.PositionName = re["PositionName"].ToString();
               
           }
           re.Close();
           return s;
       }
       public Position_Tab Posi(int id,int PositionID)
       {
           op.Open();
           if(id==1)
           {
               string sql = string.Format("select * from Position_Tab where PositionID='{0}'+1", PositionID);
               SqlCommand can = new SqlCommand(sql, op);
               SqlDataReader re = can.ExecuteReader();
               Position_Tab s = new Position_Tab();
               while(re.Read())
               {
                   s.PositionID = Convert.ToInt32(re["PositionID"]);
                   s.PositionName = re["PositionName"].ToString();
               }
               re.Close();
               return s;
           }
           else if(id==2)
           {

               string sqll = string.Format("select * from Position_Tab where PositionID='{0}'-1", PositionID);
               SqlCommand cann = new SqlCommand(sqll, op);
               SqlDataReader ree = cann.ExecuteReader();
               Position_Tab ss = new Position_Tab();
               while (ree.Read())
               {
                   ss.PositionID = Convert.ToInt32(ree["PositionID"]);
                   ss.PositionName = ree["PositionName"].ToString();
               }
               ree.Close();
               return ss;
           }
           else
           {
               return null;
           }
          
       }
       public int AddPer(PersonChange_Tab s)
       {
           op.Open();
           var sj = DateTime.Now.ToString("yy/MM/dd");
           string sql = string.Format(@"insert into PersonChange_Tab values('{0}','{1}','{2}','{3}',
           '{4}','{5}','{6}','{7}','{8}')", s.EmployID, s.DeptBefore_Id, s.DeptLasr_Id, sj,s.ChangeStatu,s.Change_Reason,s.PositionBefore_Id,s.PositionLast_Id,0);
           SqlCommand can = new SqlCommand(sql, op);
           int i = can.ExecuteNonQuery();
           op.Close();
           return i;

       }
       public List <PersonChange_Tab> person(int EmployID)
       {
           op.Open();
           string sql = string.Format(@"select a.*,b.*,c.*,d.* from PersonChange_Tab a,Position_Tab b,Dept_Tab c,EmployeeLoginInfo_Tab d where a.DeptLasr_Id=c.DeptID and
               a.PositionLast_Id=b.PositionID  and a.EmployID=d.EmployID and a.EmployID='{0}'", EmployID);
           SqlCommand can = new SqlCommand(sql,op);
           SqlDataReader re = can.ExecuteReader();
           List<PersonChange_Tab> lst = new List<PersonChange_Tab>();
           while(re.Read())
           {
               PersonChange_Tab s = new PersonChange_Tab();
               s.Situation_id = Convert.ToInt32(re["EmployID"]);
               s.EmployID = Convert.ToInt32(re["EmployID"]);
               s.EmployName = re["EmployName"].ToString();
               s.DeptLasr_Id = Convert.ToInt32(re["DeptLasr_Id"]);
               s.DeptName = re["DeptName"].ToString();
               s.PositionLast_Id = Convert.ToInt32(re["PositionLast_Id"]);
               s.PositionName = re["PositionName"].ToString();
               s.ApplyStatu = Convert.ToInt32(re["ApplyStatu"]);
               s.Change_Reason = re["Change_Reason"].ToString();
               //s.Change_time = Convert.ToDateTime(re["Change_time"]);
               s.ChangeStatu = Convert.ToInt32(re["ChangeStatu"]);
               lst.Add(s);
           }
           re.Close();
           return lst;

       }
       public PersonChange_Tab demand(int Situation_id)
       {
           op.Open();
           string sql = string.Format("select ApplyStatu from PersonChange_Tab where Situation_id='{0}'",Situation_id);
           SqlCommand can = new SqlCommand(sql, op);
           SqlDataReader re = can.ExecuteReader();
           PersonChange_Tab s = new PersonChange_Tab();
           while(re.Read())
           {
               s.ApplyStatu = Convert.ToInt32(re["ApplyStatu"]);
           }
           re.Close();
           return s;
       }
       public List<PersonChange_Tab> YiJi(int PositionID, int DeptID)
       {
           op.Open();
      
               string sql = string.Format("select a.*,b.*,c.*,d.* from PersonChange_Tab a,EmployeeLoginInfo_Tab b,Position_Tab c,Dept_Tab d where a.PositionBefore_Id=c.PositionID and a.DeptBefore_Id=d.DeptID and a.EmployID=b.EmployID and a.ApplyStatu=0 and a.DeptBefore_Id='{0}' and a.PositionBefore_Id='{1}'-2", DeptID, PositionID);
               SqlCommand can = new SqlCommand(sql, op);
               SqlDataReader re = can.ExecuteReader();
               List<PersonChange_Tab> lst = new List<PersonChange_Tab>();
               while (re.Read())
               {
                   PersonChange_Tab s = new PersonChange_Tab();
                   s.Situation_id = Convert.ToInt32(re["Situation_id"]);
                   s.EmployID = Convert.ToInt32(re["EmployID"]);
                   s.EmployName = re["EmployName"].ToString();
                   s.DeptLasr_Id = Convert.ToInt32(re["DeptLasr_Id"]);
                   s.DeptName = re["DeptName"].ToString();
                   s.PositionLast_Id = Convert.ToInt32(re["PositionLast_Id"]);
                   s.PositionName = re["PositionName"].ToString();
                   s.ApplyStatu = Convert.ToInt32(re["ApplyStatu"]);
                   s.Change_Reason = re["Change_Reason"].ToString();
                   //s.Change_time = Convert.ToDateTime(re["Change_time"]);
                   s.ChangeStatu = Convert.ToInt32(re["ChangeStatu"]);
                   lst.Add(s);
               }
               re.Close();
               return lst;
      
               //string sqll = string.Format("select a.*,b.*,c.*,d.* from PersonChange_Tab a,EmployeeLoginInfo_Tab b,Position_Tab c,Dept_Tab d where a.PositionBefore_Id=c.PositionID and a.DeptBefore_Id=d.DeptID and a.EmployID=b.EmployID and a.ApplyStatu=1 and a.DeptBefore_Id='{0}' and a.PositionBefore_Id='{1}'-2", DeptID, PositionID);
               //SqlCommand cann = new SqlCommand(sqll, op);
               //SqlDataReader ree = cann.ExecuteReader();
               //List<PersonChange_Tab> lstt = new List<PersonChange_Tab>();
               //while (ree.Read())
               //{
               //    PersonChange_Tab ss = new PersonChange_Tab();
               //    ss.Situation_id = Convert.ToInt32(ree["Situation_id"]);
               //    ss.EmployID = Convert.ToInt32(ree["EmployID"]);
               //    ss.EmployName = ree["EmployName"].ToString();
               //    ss.DeptLasr_Id = Convert.ToInt32(ree["DeptLasr_Id"]);
               //    ss.DeptName = ree["DeptName"].ToString();
               //    ss.PositionLast_Id = Convert.ToInt32(ree["PositionLast_Id"]);
               //    ss.PositionName = ree["PositionName"].ToString();
               //    ss.ApplyStatu = Convert.ToInt32(ree["ApplyStatu"]);
               //    ss.Change_Reason = ree["Change_Reason"].ToString();
               //    //s.Change_time = Convert.ToDateTime(re["Change_time"]);
               //    ss.ChangeStatu = Convert.ToInt32(ree["ChangeStatu"]);
               //    lstt.Add(ss); 
               //}
               //ree.Close();
               //return lstt;
    
         
       }
         public List<PersonChange_Tab> Er(int PositionID, int DeptID)
       {
           op.Open();

           string sql = string.Format("select a.*,b.*,c.*,d.* from PersonChange_Tab a,EmployeeLoginInfo_Tab b,Position_Tab c,Dept_Tab d where a.PositionBefore_Id=c.PositionID and a.DeptLasr_Id=d.DeptID and a.EmployID=b.EmployID and a.ApplyStatu=0 and a.DeptBefore_Id='{0}' and a.PositionBefore_Id='{1}'-2", DeptID, PositionID);
           SqlCommand can = new SqlCommand(sql, op);
           SqlDataReader re = can.ExecuteReader();
           List<PersonChange_Tab> lst = new List<PersonChange_Tab>();
           while (re.Read())
           {
               PersonChange_Tab s = new PersonChange_Tab();
               s.Situation_id = Convert.ToInt32(re["Situation_id"]);
               s.EmployID = Convert.ToInt32(re["EmployID"]);
               s.EmployName = re["EmployName"].ToString();
               s.DeptLasr_Id = Convert.ToInt32(re["DeptLasr_Id"]);
               s.DeptName = re["DeptName"].ToString();
               s.PositionLast_Id = Convert.ToInt32(re["PositionLast_Id"]);
               s.PositionName = re["PositionName"].ToString();
               s.ApplyStatu = Convert.ToInt32(re["ApplyStatu"]);
               s.Change_Reason = re["Change_Reason"].ToString();
               //s.Change_time = Convert.ToDateTime(re["Change_time"]);
               s.ChangeStatu = Convert.ToInt32(re["ChangeStatu"]);
               lst.Add(s);
           }
           re.Close();
           return lst;
       }
       public  List<PersonChange_Tab> Ren()
         {
             op.Open();
             string sql = string.Format("select a.*,b.*,c.*,d.* from PersonChange_Tab a,EmployeeLoginInfo_Tab b,Position_Tab c,Dept_Tab d where a.PositionBefore_Id=c.PositionID and a.DeptLasr_Id=d.DeptID and a.EmployID=b.EmployID");
             SqlCommand can = new SqlCommand(sql, op);
             SqlDataReader re = can.ExecuteReader();
             List<PersonChange_Tab> lst = new List<PersonChange_Tab>();
             while (re.Read())
             {
                 PersonChange_Tab s = new PersonChange_Tab();
                 s.Situation_id = Convert.ToInt32(re["Situation_id"]);
                 s.EmployID = Convert.ToInt32(re["EmployID"]);
                 s.EmployName = re["EmployName"].ToString();
                 s.DeptLasr_Id = Convert.ToInt32(re["DeptLasr_Id"]);
                 s.DeptName = re["DeptName"].ToString();
                 s.PositionLast_Id = Convert.ToInt32(re["PositionLast_Id"]);
                 s.PositionName = re["PositionName"].ToString();
                 s.ApplyStatu = Convert.ToInt32(re["ApplyStatu"]);
                 s.Change_Reason = re["Change_Reason"].ToString();
                 //s.Change_time = Convert.ToDateTime(re["Change_time"]);
                 s.ChangeStatu = Convert.ToInt32(re["ChangeStatu"]);
                 lst.Add(s);
             }
             re.Close();
             return lst;
         }
     
       public int Tong(int Situation_id)
       {
           op.Open();
           string sql = string.Format("update  PersonChange_Tab set ApplyStatu=1 where Situation_id='{0}'", Situation_id);
           SqlCommand can = new SqlCommand(sql, op);
           int i = can.ExecuteNonQuery();
           return i;
       }
       public int BuTong(int Situation_id)
       {
           op.Open();
           string sql = string.Format("update  PersonChange_Tab set ApplyStatu=-1 where Situation_id='{0}'", Situation_id);
           SqlCommand can = new SqlCommand(sql, op);
           int i = can.ExecuteNonQuery();
           return i;
       }
    
    }
}
